﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Threading;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using SoufunData.DB;

namespace SoufunData
{
    public partial class ExToDbZProjectDetail : DevExpress.XtraEditors.XtraForm
    {
        public ExToDbZProjectDetail()
        {
            InitializeComponent();
        }

        private void btn_excelPath_ButtonClick(object sender, DevExpress.XtraEditors.Controls.ButtonPressedEventArgs e)
        {

            openFileDialog1.Filter = "Excel文件|*.xls;*.xlsx";
            openFileDialog1.FileName = "";
            openFileDialog1.ShowDialog();
            string fileName = openFileDialog1.FileName;
            btn_excelPath.Text = fileName;

            gridControl2.DataSource = null;
            gridView2.Columns.Clear();
            gridView2.GroupPanelText = "请选择Excel文件";
            if (fileName == "")
            {
                return;
            }
            DataSet ds = Common.LoadDataFromExcel(fileName);
            if (ds == null || ds.Tables.Count == 0 )
            {
                string strError = "读取Excel时发生错误，请核对：\r\n ";
                strError += "1，导入时请在Excel中关闭想要导入的文件！\r\n";
                strError += "2，导入的Excel表中的工作表名称必须为Sheet1,否者程序无法读取！\r\n";
                strError += "3，请确保您的计算机安装了Microsoft Office！\r\n";
                strError += "4，请确保导入的Excel文件正常！";
                XtraMessageBox.Show(strError);
                btn_excelPath.Text = "";
                return;
            }
            if (!CheckExcelColumnsName(ds.Tables[0]))
            {
                btn_excelPath.Text = "";
                return;
            }
            gridControl2.DataSource = ds.Tables[0];
            gridView2.GroupPanelText = "共有" + ds.Tables[0].Rows.Count + "条数据";
        }


        private void AddData()
        {
           
            DataTable dtOld = (DataTable)gridControl2.DataSource;

            DataTable dt = dtOld.Copy();
            dt.Rows.Clear();
            foreach (DataRow dataRow in dtOld.Rows)
            {
                 
                dt.Rows.Add(dataRow.ItemArray);
            }
             
            int insertRowsCount = 500;//一次性插入数据库行数
            int succeedrows = 0;//成功插入数据行数
            StringBuilder strSql = new StringBuilder();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow row = dt.Rows[i];
                string strProjectName = row["项目名称"].ToString();
                string strAddress = row["项目地址"].ToString();
                string strLoop = row["环线位置"].ToString();
                string strXzq = row["行政区"].ToString();
                string strBoard = row["版块"].ToString();
                string strRongJiLv = row["容积率"].ToString();
                string strQuHuaLv = row["绿化率"].ToString();
                string strPropertyType = row["物业类型"].ToString();
                string strBuildingStyle = row["建筑风格"].ToString();
                string strBuildingShape = row["建筑形态"].ToString();
                string strDeveloper = row["开发商"].ToString();
                string strPropertyCompany = row["物业公司"].ToString();
                string strProxyCompany = row["代理公司"].ToString();
                string strBuildCompany = row["建筑公司"].ToString();
                string strSceneDesign = row["园林景观设计公司"].ToString();
                string strPropertyPrice = row["物业费"].ToString();
                string strHandingTime = row["交房时间"].ToString();
                string strHandingBuilding = row["交房楼栋"].ToString();
                string strCheckInRate = row["入住率"].ToString();
                string strHireRate = row["出租率"].ToString();
                string strTrimType = row["装修类型"].ToString();
                string strTrimPrice = row["装修标准"].ToString();
                string strArea = row["占地面积"].ToString();
                string strTotalArea = row["总建筑面积"].ToString();
                string strHouseTypeImage = row["户型图"].ToString();
                string strLouShuImage = row["楼书设计"].ToString();
                string strYbfImage = row["样板间图片"].ToString();

                if (i%insertRowsCount == 0)
                {
                    strSql = new StringBuilder();
                    strSql.Append("Insert into tzprojectdetail (");
                    strSql.Append("ProjectName,");
                    strSql.Append("Address,");
                    strSql.Append("`Loop`,");
                    strSql.Append("AdministratiVeregion,");
                    strSql.Append("Board,");
                    strSql.Append("RongJiLv,");
                    strSql.Append("QuHuaLv,");
                    strSql.Append("PropertyType,");
                    strSql.Append("BuildingStyle,");
                    strSql.Append("BuildingShape,");
                    strSql.Append("Developer,");
                    strSql.Append("PropertyCompany,");
                    strSql.Append("ProxyCompany,");
                    strSql.Append("BuildCompany,");
                    strSql.Append("SceneDesign,");
                    strSql.Append("PropertyPrice,");
                    strSql.Append("HandingTime,");
                    strSql.Append("HandingBuilding,");
                    strSql.Append("CheckInRate,");
                    strSql.Append("HireRate,");
                    strSql.Append("TrimType,");
                    strSql.Append("TrimPrice,");
                    strSql.Append("Area,");
                    strSql.Append("TotalArea,");
                    strSql.Append("HouseTypeImage,");
                    strSql.Append("LouShuImage,");
                    strSql.Append("YangBanImage ");
                    strSql.Append(") value  ");
                }
                strSql.Append("(");
                strSql.Append("'");
                strSql.Append(strProjectName);
                strSql.Append("','");
                strSql.Append(strAddress);
                strSql.Append("','");
                strSql.Append(strLoop);
                strSql.Append("','");
                strSql.Append(strXzq);
                strSql.Append("','");
                strSql.Append(strBoard);
                strSql.Append("','");
                strSql.Append(strRongJiLv);
                strSql.Append("','");
                strSql.Append(strQuHuaLv);
                strSql.Append("','");
                strSql.Append(strPropertyType);
                strSql.Append("','");
                strSql.Append(strBuildingStyle);
                strSql.Append("','");
                strSql.Append(strBuildingShape);
                strSql.Append("','");
                strSql.Append(strDeveloper);
                strSql.Append("','");
                strSql.Append(strPropertyCompany);
                strSql.Append("','");
                strSql.Append(strProxyCompany);
                strSql.Append("','");
                strSql.Append(strBuildCompany);
                strSql.Append("','");
                strSql.Append(strSceneDesign);
                strSql.Append("','");
                strSql.Append(strPropertyPrice);
                strSql.Append("','");
                strSql.Append(strHandingTime);
                strSql.Append("','");
                strSql.Append(strHandingBuilding);
                strSql.Append("','");
                strSql.Append(strCheckInRate);
                strSql.Append("','");
                strSql.Append(strHireRate);
                strSql.Append("','");
                strSql.Append(strTrimType);
                strSql.Append("','");
                strSql.Append(strTrimPrice);
                strSql.Append("',");
                strSql.Append(strArea);
                strSql.Append(",");
                strSql.Append(strTotalArea);
                strSql.Append(",'");
                strSql.Append(strHouseTypeImage);
                strSql.Append("','");
                strSql.Append(strLouShuImage);
                strSql.Append("','");
                strSql.Append(strYbfImage);                 
                strSql.Append("'),");

                if ((i + 1) % insertRowsCount == 0
                    || i == dt.Rows.Count - 1)
                {
                    strSql.Remove(strSql.Length - 1, 1);
                    succeedrows += DbHelper.ExecuteSql(strSql.ToString());
                }
            }

            if (dt.Rows.Count == succeedrows)
            {
                XtraMessageBox.Show("添加成功！");
                BeginInvoke(new MethodInvoker(() =>
                {
                    btn_excelPath.Text = "";
                    gridControl2.DataSource = null;
                }));
            }
            else
            {
                int failedCount = dt.Rows.Count - succeedrows;
                XtraMessageBox.Show("共记" + dt.Rows.Count + "条数据，其中"
                    + failedCount + "条数据添加失败！");
            }
            BeginInvoke(new MethodInvoker(() =>
            {
               
                btn_excelPath.Enabled = true;
                btn_add.Enabled = true;
            }));
        }

        private void btn_add_Click(object sender, EventArgs e)
        {
            if (btn_excelPath.Text.Trim()=="")
            {
                XtraMessageBox.Show("请先选择Excel！");
                return;
            }
            if (!CheckDataValid())
            {
                return;
            }
             
            btn_excelPath.Enabled = false;
            btn_add.Enabled = false;
            Thread t = new Thread(AddData) { IsBackground = true };
            t.Start();
        }

        #region 数据完整性检测
        private bool CheckDataValid()
        {
            DataTable dt = (DataTable)gridControl2.DataSource;
            StringBuilder strErrors = new StringBuilder();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                string strProName = dr["项目名称"].ToString();
                string strHuanxian = dr["环线位置"].ToString();
                string strXzq = dr["行政区"].ToString();
                string strBoard = dr["版块"].ToString();
                string strArea = dr["占地面积"].ToString();
                string strTotalArea = dr["总建筑面积"].ToString();

                if (strHuanxian.Trim() == "")
                {
                    strErrors.Append("第" + (i + 2) + "行数据中的【环线位置】为空！\r\n");
                }             
                if (strProName.Trim() == "")
                {
                    strErrors.Append("第" + (i + 2) + "行数据中的【项目名称】为空！\r\n");
                }
                if (strXzq.Trim() == "")
                {
                    strErrors.Append("第" + (i + 2) + "行数据中【行政区】未填！\r\n");
                }
                if (strBoard.Trim() == "")
                {
                    strErrors.Append("第" + (i + 2) + "行数据中【板块】未填！\r\n");
                }
                float floatTem = 0;
                if (!float.TryParse(strArea,out floatTem))
                {
                    strErrors.Append("第" + (i + 2) + "行数据中【占地面积】不是正确的数字格式！\r\n");
                }
                if (!float.TryParse(strTotalArea, out floatTem))
                {
                    strErrors.Append("第" + (i + 2) + "行数据中【总建筑面积】不是正确的数字格式！\r\n");
                }
            }
            if (strErrors.ToString() != "")
            {
                XtraMessageBox.Show("您选择的Excel中的数据存在以下错误：\r\n" +
                    strErrors);
                return false;
            }
            return true;
        }

        private bool CheckExcelColumnsName(DataTable dt)
        {
            StringBuilder strErrors = new StringBuilder();
            if (!dt.Columns.Contains("项目名称"))
            {
                strErrors.Append("【项目名称】、");
            }
            if (!dt.Columns.Contains("项目地址"))
            {
                strErrors.Append("【项目地址】、");
            }
            if (!dt.Columns.Contains("环线位置"))
            {
                strErrors.Append("【环线位置】、");
            }
            if (!dt.Columns.Contains("行政区"))
            {
                strErrors.Append("【行政区】、");
            }
            if (!dt.Columns.Contains("版块"))
            {
                strErrors.Append("【版块】、");
            }
            if (!dt.Columns.Contains("容积率"))
            {
                strErrors.Append("【容积率】、");
            }
            if (!dt.Columns.Contains("绿化率"))
            {
                strErrors.Append("【绿化率】、");
            }
            if (!dt.Columns.Contains("物业类型"))
            {
                strErrors.Append("【物业类型】、");
            }
            if (!dt.Columns.Contains("建筑风格"))
            {
                strErrors.Append("【建筑风格】、");
            }
            if (!dt.Columns.Contains("建筑形态"))
            {
                strErrors.Append("【建筑形态】、");
            }
            if (!dt.Columns.Contains("开发商"))
            {
                strErrors.Append("【开发商】");
            }
            if (!dt.Columns.Contains("物业公司"))
            {
                strErrors.Append("【物业公司】");
            }
            if (!dt.Columns.Contains("代理公司"))
            {
                strErrors.Append("【代理公司】");
            }
            if (!dt.Columns.Contains("建筑公司"))
            {
                strErrors.Append("【建筑公司】");
            }
            if (!dt.Columns.Contains("园林景观设计公司"))
            {
                strErrors.Append("【园林景观设计公司】");
            }
            if (!dt.Columns.Contains("物业费"))
            {
                strErrors.Append("【物业费】");
            }
            if (!dt.Columns.Contains("交房时间"))
            {
                strErrors.Append("【交房时间】");
            }
            if (!dt.Columns.Contains("交房楼栋"))
            {
                strErrors.Append("【交房楼栋】");
            }
            if (!dt.Columns.Contains("入住率"))
            {
                strErrors.Append("【入住率】");
            }
            if (!dt.Columns.Contains("出租率"))
            {
                strErrors.Append("【出租率】");
            }
            if (!dt.Columns.Contains("装修类型"))
            {
                strErrors.Append("【装修类型】");
            }
            if (!dt.Columns.Contains("装修标准"))
            {
                strErrors.Append("【装修标准】");
            }
            if (!dt.Columns.Contains("占地面积"))
            {
                strErrors.Append("【占地面积】");
            }
            if (!dt.Columns.Contains("总建筑面积"))
            {
                strErrors.Append("【总建筑面积】");
            }
            if (!dt.Columns.Contains("户型图"))
            {
                strErrors.Append("【户型图】");
            }
            if (!dt.Columns.Contains("楼书设计"))
            {
                strErrors.Append("【楼书设计】");
            }
            if (!dt.Columns.Contains("样板间图片"))
            {
                strErrors.Append("【样板间图片】");
            }
            if (strErrors.ToString() != "")
            {
                XtraMessageBox.Show("您选择的Excel表数据不正常,缺少以下数据列:\r\n" +
                                    strErrors);
                return false;
            }
            return true;
        }
        #endregion
    }
}